﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace lcps.Web.supplyplan
{
    public partial class importPlan : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnimport_Click(object sender, EventArgs e)
        {
            string filename = string.Empty;
            try
            {
                filename = UpLoadXls(FileExcel);//上传XLS文件
                ImportXlsToData(filename);//将XLS文件的数据导入数据库               
                if (filename != string.Empty && System.IO.File.Exists(filename))
                {
                    System.IO.File.Delete(filename);//删除上传的XLS文件
                }
                Response.Redirect("list.aspx");
            }
            catch (Exception ex)
            {
                LblMessage.Text = ex.Message;
            }

        }
        private void ImportXlsToData(string fileName)
        {
            try
            {
                if (fileName == string.Empty)
                {
                    throw new ArgumentNullException("Excel文件上传失败！");
                }
                string oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + "; Extended Properties='Excel 8.0;'";
                OleDbConnection oleDBConn = null;
                OleDbDataAdapter oleAdMaster = null;
                DataTable m_tableName = new DataTable();
                DataSet ds = new DataSet();
                oleDBConn = new OleDbConnection(oleDBConnString);
                oleDBConn.Open();
                m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (m_tableName != null && m_tableName.Rows.Count > 0)
                {
                    m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString(); 
                }
                string sqlMaster;
                sqlMaster = " SELECT *  FROM [" + m_tableName.TableName + "]";
                oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
                oleAdMaster.Fill(ds, "m_tableName");
                oleAdMaster.Dispose();
                // oleDBConn.Close();
                oleDBConn.Dispose();
                AddDatasetToSQL(ds, 12);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
        {
            string orifilename = string.Empty;
            string uploadfilepath = string.Empty;
            string modifyfilename = string.Empty;
            string fileExtend = "";//文件扩展名
            int fileSize = 0;//文件大小
            try
            {
                if (inputfile.Value != string.Empty)
                {
                    //得到文件的大小
                    fileSize = inputfile.PostedFile.ContentLength;
                    if (fileSize == 0)
                    {
                        throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
                    }
                    //得到扩展名
                    fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                    if (fileExtend.ToLower() != "xls")
                    {
                        throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
                    }
                    //路径
                    uploadfilepath = Server.MapPath("~/upload/");
                    //新文件名
                    modifyfilename = System.Guid.NewGuid().ToString();
                    //modifyfilename = inputfile.PostedFile.FileName;
                    modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                    //判断是否有该目录
                    System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
                    if (!dir.Exists)
                    {
                        dir.Create();
                    }
                    orifilename = uploadfilepath + "\\" + modifyfilename;
                    //如果存在,删除文件
                    if (File.Exists(orifilename))
                    {
                        File.Delete(orifilename);
                    }
                    // 上传文件
                    inputfile.PostedFile.SaveAs(orifilename);
                }
                else
                {
                    throw new Exception("请选择要导入的Excel文件!");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return orifilename;
        }
        private bool AddDatasetToSQL(DataSet pds, int Cols)
        {
            lcps.BLL.supplyplan bs = new BLL.supplyplan();
            lcps.Model.supplyplan model;

            int ic, ir;
            ic = pds.Tables[0].Columns.Count;
            if (pds.Tables[0].Columns.Count < Cols)
            {
                throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
            }
            //项目编号	招标计划编号	采购订单号	需用单位	项目名称	项目类型	供应商名称	物料描述	数量	单位	合同签定日期	合同交货时间	项目单位要求到货时间	项目计划投运时间	图纸设计计划完成时间	目前图纸设计情况	图纸应提交设计单位时间	要求设计单位确认时间	设计单位确认情况	主要原材料采购情况	目前设备生产状态	本月生产情况	下月生产计划	计划到货时间	需要协调的问题	备注	实际到货时间	接收人	联系电话	供应商联系人	联系电话
            ir = pds.Tables[0].Rows.Count;
            if (pds != null && pds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < pds.Tables[0].Rows.Count; i++)
                {

                    model = new Model.supplyplan();
                    model.xh = pds.Tables[0].Rows[i][0].ToString();
                    if (model.xh.Trim() != "" && model.xh.Trim() != "序号")
                    {
                        if (pds.Tables[0].Rows[i][1].ToString().Trim() != "")
                            model.plandate = DateTime.Parse(pds.Tables[0].Rows[i][1].ToString());
                        model.projectname = pds.Tables[0].Rows[i][2].ToString();
                        model.projectid = pds.Tables[0].Rows[i][3].ToString();
                        model.wbs = pds.Tables[0].Rows[i][4].ToString();
                        model.xyunit = pds.Tables[0].Rows[i][5].ToString();
                        model.materuser = pds.Tables[0].Rows[i][6].ToString();
                        model.projectunit = pds.Tables[0].Rows[i][7].ToString();
                        model.projectmajor= pds.Tables[0].Rows[i][8].ToString();
                        model.plannum = pds.Tables[0].Rows[i][9].ToString();
                                              
                        if (pds.Tables[0].Rows[i][10].ToString().Trim() != "")
                            model.planoverdate = DateTime.Parse(pds.Tables[0].Rows[i][10].ToString());
                        
                        model.addplannum = pds.Tables[0].Rows[i][11].ToString();
                       
                        bs.Add(model);
                    }
                }
            }
            else
            {
                throw new Exception("导入数据为空！");
            }
            return true;
        }   
    }
}